﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections;
using MySql.Data.MySqlClient;




/// <summary>
/// DepartmentDb 的摘要说明
/// </summary>
public class DepartmentDb : CommonDb
{
	public DepartmentDb()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}

    public string GetDepartmentNameById(int Id)
    {
        string dptName = null;
        MySqlCommand cmd = new MySqlCommand("select Name from department where Id=" + Id, m_conn);
        try
        {
            m_conn.Open();
            MySqlDataReader reader = cmd.ExecuteReader();

            if (reader.Read())
            {
                dptName = (string)reader["Name"];
            }
            reader.Close();

            return dptName;
        }
        catch (MySqlException err)
        {
            // Replace the error with something less specific.
            // You could also log the error now.
            throw new ApplicationException("Data error.");
        }
        finally
        {
            m_conn.Close();
        }
    }

    public DepartmentDetail GetDepartmentsById(int DepartmentNum)
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("GetDepartmentById", m_conn);
        DataComponent.AddCmdParameters(cmd, "?dptId", MySqlDbType.Int32, 10, DepartmentNum);
        
        DepartmentDetail dpt = new DepartmentDetail();

        try
        {
            m_conn.Open();
            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                
                dpt.Id = (int)reader["Id"];
                dpt.Name = (string)reader["Name"];
                dpt.Score = (int)reader["DepartmentScore"];
                dpt.ScoreBySegment = (int)reader["ScoreBySegment"];
                dpt.LeaderName = (string)reader["LeaderName"];
               
            }
            reader.Close();

            return dpt;
        }
        catch (MySqlException err)
        {
            // Replace the error with something less specific.
            // You could also log the error now.
            throw new ApplicationException(err.ToString());
        }
        finally
        {
            m_conn.Close();
        }
    }
    
    public int[] GetDirectors()
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("GetDirectors", m_conn);
        cmd.CommandText = "select distinct DirectorSN from department";
        cmd.CommandType = CommandType.Text;

        ArrayList Directors = new ArrayList();

        try
        {
            m_conn.Open();
            MySqlDataReader reader = cmd.ExecuteReader();
            while(reader.Read())
            {
                int DirectorSN = (int)reader["DirectorSN"];
                Directors.Add(DirectorSN);
            }
            reader.Close();
            return (int[])Directors.ToArray(typeof(int));
        }
        catch(MySqlException err)
        {
            throw new ApplicationException(err.ToString());
        }
        finally
        {
            m_conn.Close();
        }
    }

    public DataSet GetDepartmentsByManager()
    {
        DataSet ds = new DataSet();
        MySqlDataAdapter da = new MySqlDataAdapter("select * from department", m_conn);
        try
        {
            m_conn.Open();
            da.Fill(ds, "departments");
            return ds;
        }
        catch(MySqlException err)
        {
            throw new ApplicationException(err.ToString());
        }
        finally
        {
            m_conn.Close();
        }
    }

    public DataSet GetDepartmentsByDirector(int DirectorSN)
    {
        
        DataSet DepartmentInfo = new DataSet();
        string sqlcmd1 = "select * from department where DirectorSN = " + DirectorSN.ToString();
        string sqlcmd2 = "select Name, SelfScore, LeaderScore from staff";
        MySqlDataAdapter da = new MySqlDataAdapter(sqlcmd1, m_conn);
        try
        {
            m_conn.Open();
            da.Fill(DepartmentInfo, "Department");
            da.SelectCommand.CommandText = sqlcmd2;
            da.Fill(DepartmentInfo, "Staff");

            DataRelation rela = new DataRelation("DepartmentLeader", DepartmentInfo.Tables["Staff"].Columns["Name"] ,
                DepartmentInfo.Tables["Department"].Columns["LeaderName"]               
                );

            DepartmentInfo.Relations.Add(rela);
            return DepartmentInfo;
        }
        finally
        {

        }
        
        //MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("GetDptsByDirector", m_conn);
        //DataComponent.AddCmdParameters(cmd, "?dtSN", MySqlDbType.Int32, 10, DirectorSN);
        //// Create a collection for all the employee records.
        //ArrayList departments = new ArrayList();

        //try
        //{
        //    m_conn.Open();
        //    MySqlDataReader reader = cmd.ExecuteReader();

        //    while (reader.Read())
        //    {
        //        DepartmentDetail dpt = new DepartmentDetail();
        //        dpt.Id = (int)reader["Id"];
        //        dpt.Name = (string)reader["Name"];
        //        dpt.Score = (int)reader["DepartmentScore"];
        //        dpt.ScoreBySegment = (int)reader["ScoreBySegment"];
        //        dpt.LeaderName = (string)reader["LeaderName"];
        //        dpt.DirectorSN = (int)reader["DirectorSN"];
        //        departments.Add(dpt);
        //    }
        //    reader.Close();

        //    return (DepartmentDetail[])departments.ToArray(typeof(DepartmentDetail));
        //}
        //catch (MySqlException err)
        //{
        //    // Replace the error with something less specific.
        //    // You could also log the error now.
        //    throw new ApplicationException("Data error.");
        //}
        //finally
        //{
        //    m_conn.Close();
        //}
    }

    public DepartmentDetail[] GetDepartments()
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("GetDepartments", m_conn);

        // Create a collection for all the employee records.
        ArrayList departments = new ArrayList();

        try
        {
            m_conn.Open();
            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                DepartmentDetail dpt = new DepartmentDetail();
                dpt.Id = (int)reader["Id"];
                dpt.Name = (string)reader["Name"];
                dpt.Score = (int)reader["DepartmentScore"];
                dpt.ScoreBySegment = (int)reader["ScoreBySegment"];
                dpt.LeaderName = (string)reader["LeaderName"];
                dpt.DirectorSN = (int)reader["DirectorSN"];
                departments.Add(dpt);
            }
            reader.Close();

            return (DepartmentDetail[])departments.ToArray(typeof(DepartmentDetail));
        }
        catch (MySqlException err)
        {
            // Replace the error with something less specific.
            // You could also log the error now.
            throw new ApplicationException("Data error.");
        }
        finally
        {
            m_conn.Close();
        }
    }

    public void UpdateDptLeader(int ScoreBySegment, int LeaderScore, int Id, string LeaderName)
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("UpdateDptLeader", m_conn);
        DataComponent.AddCmdParameters(cmd, "?dptScoreBySegment", MySqlDbType.Int32, 10, ScoreBySegment);
        DataComponent.AddCmdParameters(cmd, "?dptLeaderScore", MySqlDbType.Int32, 10, LeaderScore);
        DataComponent.AddCmdParameters(cmd, "?dptId", MySqlDbType.Int32, 10, Id);
        DataComponent.AddCmdParameters(cmd, "?dptLeaderName", MySqlDbType.VarChar, 45, LeaderName);

        DataComponent.ExecNonQueryCmd(cmd, m_conn);
    }

    public void UpdateDepartmentScoreBySN(DepartmentDetail dptInfo)
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("UpdateDptScoreById", m_conn);
        DataComponent.AddCmdParameters(cmd, "?DptId", MySqlDbType.Int32, 10, dptInfo.Id);
        DataComponent.AddCmdParameters(cmd, "?DptScore", MySqlDbType.Int32, 10, dptInfo.Score);
        DataComponent.AddCmdParameters(cmd, "?dptScoreBySegment", MySqlDbType.Int32, 10, dptInfo.ScoreBySegment);
        
        ExecNonQueryCmd(cmd);
    }

    private void ExecNonQueryCmd(MySqlCommand cmd)
    {
        try
        {
            m_conn.Open();
            cmd.ExecuteNonQuery();
        }
        catch (MySqlException err)
        {
            throw new ApplicationException(err.ToString());
        }
        finally
        {
            m_conn.Close();
        }
    }
    public void UpdateDepartment(DepartmentDetail dpt)
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("UpdateDepartment", m_conn);
        DataComponent.AddCmdParameters(cmd, "?dpId", MySqlDbType.Int32, 10, dpt.Id);
        DataComponent.AddCmdParameters(cmd, "?dpName", MySqlDbType.VarChar, 45, dpt.Name);
        DataComponent.AddCmdParameters(cmd, "?dpLeaderName", MySqlDbType.VarChar, 45, dpt.LeaderName);
        DataComponent.AddCmdParameters(cmd, "?dpDirectorSN", MySqlDbType.Int32, 10, dpt.DirectorSN);
        ExecNonQueryCmd(cmd);

    }

    public int InsertDepartment(DepartmentDetail dpt)
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("InsertDepartment", m_conn);
        DataComponent.AddCmdParameters(cmd, "?dpName", MySqlDbType.VarChar, 45, dpt.Name);
        DataComponent.AddCmdParameters(cmd, "?dpLeaderName", MySqlDbType.VarChar, 45, dpt.LeaderName);
        DataComponent.AddCmdOutParameters(cmd, "?dpId", MySqlDbType.Int32, 10);
        
        try
        {
            m_conn.Open();
            cmd.ExecuteNonQuery();
            return (int)cmd.Parameters["?dpId"].Value;
        }
        catch(MySqlException err)
        {
             throw new ApplicationException("Data error.");
        }
        finally
        {
            m_conn.Close();

        }
    }

    public void DeleteDepartment(DepartmentDetail dpt)
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("DeleteDepartment", m_conn);
        DataComponent.AddCmdParameters(cmd, "?dpId", MySqlDbType.Int32, 10, dpt.Id);
        try
        {
            m_conn.Open();
            cmd.ExecuteNonQuery();
        }
        catch(MySqlException err)
        {
            throw new ApplicationException("Data error");
        }
        finally
        {
            m_conn.Close();
        }
    }

    
}
